package PrjHttpServ

import (
	"dgo/MyProj/PrjDBUtils"
	"dgo/MyProj/PrjHttpUtils"
	db_utils "dgo/goutils/db-utils"
	"dgo/goutils/utils"
	"fmt"
	"gitee.com/ymofen/supervalue"
	"math"
	"strings"
)

var (
	Default_DatasourceId string = "main"
	Default_List_KeyName string = "data"
	DBComm_Debug_Mode    int    = 0
)

const (
	TYPE_GET_RESID    int    = 0x1001
	TYPE_INIT_DAO     int    = 0x1002 // 初始化DAO
	TYPE_AFTER_UPDATE int    = 0x1003 // 更新后
	TYPE_GET_VAR      int    = 0x1004 // 获取变量
	VAR_NOT_EMPTY     string = "$$NOTEMPTY$$"
)

var (
	KEY_REQ_CNT    string = "reqcnt"
	KEY_PAGE_INDEX string = "page"
	KEY_PAGE_SIZE  string = "pagesize"
	KEY_POSI       string = "posi"
	KEY_POSI_NUM   string = "num"
)

type DBCommUpdateConf struct {
	datasourceid, dbname                                     string
	trans                                                    bool
	err_sql                                                  string
	tracesql                                                 int
	table, keyfields, ignoreupdatefields, ignoreinsertfields string
	uptype                                                   int
	batchupflag                                              int
	data                                                     *wrapper.SuperValue
	req                                                      *PrjHttpUtils.HttpRec
	OnConvertSQLValueFunc                                    func(key *string, val *interface{}) bool // 设置字段类型, 降低效率
}

/*
*

	{
	   "table":"net_goodstable",
	   "keyfields":"id",
	   "__type":0,   //0:不存在就插入, 1:表示更新, 2:插入, 3:删除
	   "data":{
	       "id":"4",
	       "goods":"4线",
	       "price":12,
	       "stock":13
	    }
	}
*/
func OnHttpDBCommUpdateBK(req *PrjHttpUtils.HttpRec) (errcode int, err error) {
	datasourceid := req.QryAsStringEx("__datasource", "", true)
	if len(datasourceid) == 0 {
		datasourceid = req.QryAsStringEx("datasourceid", "", true)
	}

	table := req.QryAsStringEx("table", "", true)
	keyfields := req.QryAsStringEx("keyfields", "", true)
	ignoreupdatefields := req.QryAsStringEx("ignoreupdatefields", "", true)
	ignoreinsertfields := req.QryAsStringEx("ignoreinsertfields", "", true)
	data := req.Req.ValueByName("data")
	if data == nil {
		data = req.Req
	}

	req.RangeURLParam(func(k, v string) bool {
		strs := strings.SplitN(k, ".", 2)
		if len(strs) == 2 && strs[0] == "data" {
			if data == nil {
				data = wrapper.NewSVObject()
			}
			data.SetKeyString(strs[1], v)
		}
		return true
	})

	var upconf DBCommUpdateConf
	upconf.datasourceid = datasourceid
	upconf.dbname = req.QryAsStringEx("__dbname", "", true)
	upconf.table = table
	upconf.keyfields = keyfields
	upconf.data = data
	upconf.ignoreupdatefields = ignoreupdatefields
	upconf.ignoreinsertfields = ignoreinsertfields
	upconf.uptype = req.QryAsInt("__type", 0)
	upconf.req = req
	return OnHttpDBCommUpdateEx(&upconf)
}

/*
*

	根据conf配置中响应配置

	{
	  "errcode":101, "msg":""
	}
*/
func responseErr(conf *wrapper.SuperValue, req *PrjHttpUtils.HttpRec) {
	errcode := conf.IntByName("errcode", 0)
	msg := conf.StringByName("msg", "")

	if errcode != 0 {
		req.SetValues("errcode", errcode)
	}
	if len(msg) > 0 {
		req.SetValues("msg", msg)
	}
	req.SetValues("code", -1)
	req.ResponseAsJSON()
}

/*
*

	根据配置, 检测变量
*/
func checkVarByResConf(conf *wrapper.SuperValue, req *PrjHttpUtils.HttpRec) (ret bool) {
	if conf == nil {
		return true
	}

	ret = true
	conf.Range(func(key string, val *wrapper.SuperValue) bool {
		if ok, v := req.ActionEx(TYPE_GET_VAR, key); ok {
			strv := utils.GetStrValue(v, "")
			matchV := val.StringByName("value", "")
			if matchV == VAR_NOT_EMPTY {
				if len(strv) == 0 {
					responseErr(val, req)
					ret = false
					return false
				}
			} else if matchV == strv || strings.Contains(matchV+",", strv+",") {
				return true
			} else {
				responseErr(val, req)
				ret = false
				return false
			}
			return true
		} else {
			responseErr(val, req)
			ret = false
			return false
		}
	})
	return
}

func ExtractUpdateData(req *PrjHttpUtils.HttpRec) *wrapper.SuperValue {
	data := req.Req.ValueByName("data")
	if data == nil {
		data = req.Req
	}
	return data
}

func ExtractQryArgsData(req *PrjHttpUtils.HttpRec) *wrapper.SuperValue {
	args := req.Req.ValueByName("args")
	if args == nil {
		args = req.Req
	}
	return args
}

func OnHttpDBCommUpdateRes(req *PrjHttpUtils.HttpRec) (errcode int, err error) {
	return innerInvokeDBCommUpdateRes(req, 0)
}

func OnHttpDBCommBatchUpdateRes(req *PrjHttpUtils.HttpRec) (errcode int, err error) {
	return innerInvokeDBCommUpdateRes(req, 1)
}

func innerInvokeDBCommUpdateRes(req *PrjHttpUtils.HttpRec, batchflag int) (errcode int, err error) {
	datasourceid := req.QryAsStringEx("__datasource", "", true)
	if len(datasourceid) == 0 {
		datasourceid = req.QryAsStringEx("datasourceid", "", true)
	}
	resid := req.QryAsStringEx("__resid", "", true)
	if len(resid) == 0 {
		resid = req.QryAsStringEx("id", "", true)
		if len(resid) == 0 {
			return ERR_INNER, fmt.Errorf("请指定资源ID(resid)[%s]", resid)
		}
	}

	var dbresrec *wrapper.SuperValue
	if req.OnActionEx != nil {
		if ok, obj := req.OnActionEx(TYPE_GET_RESID, resid); ok && obj != nil {
			dbresrec = obj.(*wrapper.SuperValue)
			if dbresrec == nil {
				return ERR_INNER, fmt.Errorf("无法找到对应的数据资源[%s]", resid)
			}
		}
	}
	if dbresrec == nil {
		dbresrec = GetDBRes(resid)
		if dbresrec == nil {
			return ERR_INNER, fmt.Errorf("无法找到对应的数据资源[%s]", resid)
		}
	}

	dbresrec.CheckStrFunc("__datasource", func(strv string) {
		datasourceid = strv
	})

	if !checkVarByResConf(dbresrec.ValueByName("check"), req) {
		return
	}

	conf := dbresrec.ValueByName("$update")
	if conf == nil {
		return ERR_INNER, fmt.Errorf("对应的数据资源[%s], $update配置不完整", resid)
	}

	var upconf DBCommUpdateConf
	upconf.table = conf.StringByName("table", "")
	upconf.keyfields = conf.StringByName("keyfields", "")
	upconf.ignoreupdatefields = conf.StringByName("ignoreupdatefields", "")
	upconf.tracesql = conf.IntByName("__tracesql", 0)
	conf_fieldtype := conf.ValueByName("fieldtype") // 设置字段类型, 降低效率
	if conf_fieldtype != nil {
		upconf.OnConvertSQLValueFunc = func(key *string, val *interface{}) bool {
			fdtype := conf_fieldtype.StringByName(*key, "")
			if len(fdtype) >= 0 {
				if fdtype == "datetime" {
					dt := utils.GetDateTimeValue(*val, utils.ZeroTime)
					if dt.IsZero() {
						return false
					}
					*val = utils.DateTimeString(dt.Local())
				}
			}
			return false
		}
	}
	if batchflag == 1 {
		upconf.batchupflag = 1
	}
	insertifempty := conf.StringByName("insertifempty", "")

	if upconf.table == "" {
		upconf.table = req.QryAsStringEx("table", "", true)
	}

	if batchflag == 1 {
		upconf.data = req.Req
	} else {
		data := req.Req.ValueByName("data")
		if data == nil {
			data = req.Req
		}
		upconf.data = data
	}

	data := upconf.data

	uptype := req.QryAsInt("__type", 0)
	if len(insertifempty) > 0 {
		if batchflag == 1 {
			lst := data.ValueByName("list")
			lst.Range(func(key string, val *wrapper.SuperValue) bool {
				if val.IfOneOfFieldsIsEmpty(insertifempty) {
					val.SetKeyValue("__uptype", db_utils.ACTION_APPEND)
				} else {
					val.SetKeyValue("__uptype", db_utils.ACTION_UPDATE)
				}
				return true
			})
		} else {
			if data.IfOneOfFieldsIsEmpty(insertifempty) {
				uptype = DBCOMM_UPTYPE_APPEND
			} else {
				uptype = DBCOMM_UPTYPE_UPDATE
			}
		}
	}

	{ // 默认插入数据
		ex := conf.ValueByName("insert")
		if ex != nil {
			data.SetKeyValue("__insert", ex)
		}
	}

	{ // 默认更新数据
		ex := conf.ValueByName("update")
		if ex != nil {
			data.SetKeyValue("__update", ex)
		}
	}

	if batchflag == 0 {
		req.RangeURLParam(func(k, v string) bool {
			strs := strings.SplitN(k, ".", 2)
			if len(strs) == 2 && strs[0] == "data" {
				if data == nil {
					data = wrapper.NewSVObject()
				}
				data.SetKeyString(strs[1], v)
			}
			return true
		})

		if uptype == DBCOMM_UPTYPE_UPDATE {
			if data.IfOneOfFieldsIsEmpty(upconf.keyfields) {
				return ERR_IN_PARAM_INVALID, fmt.Errorf("更新参数不完整:缺少更新主键对应的值!")
			}
		}
	}

	upconf.datasourceid = datasourceid
	upconf.dbname = req.QryAsStringEx("__dbname", "", true)
	upconf.uptype = uptype

	upconf.req = req
	upconf.trans = conf.BoolByName("trans", false)

	errcode, err = OnHttpDBCommUpdateEx(&upconf)
	if len(upconf.err_sql) > 0 && upconf.tracesql == 1 {
		req.Resp.SetKeyValues("errsql", upconf.err_sql)
	}
	return
}

func OnHttpDBCommUpdateEx(upconf *DBCommUpdateConf) (errcode int, err error) {
	if len(upconf.datasourceid) == 0 {
		upconf.datasourceid = Default_DatasourceId
		if len(upconf.datasourceid) == 0 {
			return ERR_INNER, fmt.Errorf("未指定数据源id(datasourceid)")
		}
	}
	req := upconf.req

	var dao *PrjDBUtils.DAOHelper
	if len(upconf.dbname) > 0 {
		dao = PrjDBUtils.NewDAOHelperEx(upconf.datasourceid, upconf.dbname)
	} else {
		dao = PrjDBUtils.NewDAOHelper(upconf.datasourceid)
	}
	defer dao.Close()
	//if req.OnActionEx != nil {
	//	req.OnActionEx(TYPE_INIT_DAO, dao)
	//}

	err = dao.PrepareDBConn()
	if err != nil {
		return ERR_INNER, err
	}

	var tracesqlBuilder utils.BytesBuilder
	if upconf.tracesql == 1 {
		dao.DBConn.OnExecSQLFunc = func(sql *string, args ...interface{}) {
			tracesqlBuilder.Appendf("%s;\r\n\r\n", *sql)
		}
	}

	dao.Updatetable = upconf.table
	dao.Keyfields = upconf.keyfields
	dao.IngoreInsertFields = upconf.ignoreinsertfields
	if upconf.trans {
		dao.DBConn.BeginTrans()
		defer dao.DBConn.AutoDoneTrans()
	}
	dao.OnConvertSQLValueFunc = upconf.OnConvertSQLValueFunc

	if req.OnActionEx != nil {
		req.OnActionEx(TYPE_INIT_DAO, dao)
	}

	var upcnt int64 = 0
	var errsql string
	uptype := upconf.uptype
	if upconf.batchupflag == 1 {
		upcnt, errsql, err = dao.BatchUpdate(upconf.data, upconf.table, upconf.keyfields, upconf.ignoreupdatefields, upconf.ignoreinsertfields, uptype, nil)
		if err != nil {
			utils.Log.WarnTypef("DB", "sql:%s, err:%s", errsql, err)
			upconf.err_sql = errsql
			return ERR_DB_ERR, err
		}
	} else if uptype == DBCOMM_UPTYPE_APPEND { // 插入
		if req.OnActionEx != nil {
			ok, r1 := req.OnActionEx(DBCOMM_UPTYPE_APPEND, dao)
			if ok && r1 != nil && r1 == false {
				return
			}
		}
		upcnt, errsql, err = dao.InsertEx2(upconf.data, upconf.table, upconf.ignoreinsertfields, nil, nil)
		if err != nil {
			utils.Log.WarnTypef("DB", "sql:%s, err:%s", errsql, err)
			upconf.err_sql = errsql
			return ERR_DB_ERR, err
		}
	} else if uptype == DBCOMM_UPTYPE_UPDATE { // 更新
		if len(dao.Keyfields) == 0 {
			return ERR_IN_PARAM_INVALID, fmt.Errorf("缺少指定更新主键!!!")
		}
		upcnt, errsql, err = dao.UpdateEx2(upconf.data, upconf.table, upconf.keyfields, upconf.ignoreupdatefields, nil, nil)
		if err != nil {
			utils.Log.WarnTypef("DB", "sql:%s, err:%s", errsql, err)
			upconf.err_sql = errsql
			return ERR_DB_ERR, err
		}
	} else if uptype == DBCOMM_UPTYPE_DELETE { // 删除
		if req.OnActionEx != nil {
			ok, r1 := req.OnActionEx(DBCOMM_UPTYPE_DELETE, dao)
			if ok && r1 != nil && r1 == false {
				return
			}
		}
		if upconf.data.Count() == 0 {
			return ERR_IN_PARAM_INVALID, fmt.Errorf("缺少指定删除条件!!!")
		}
		upcnt, errsql, err = dao.Delete(upconf.data, upconf.keyfields, nil)
		if err != nil {
			utils.Log.WarnTypef("DB", "sql:%s, err:%s", errsql, err)
			upconf.err_sql = errsql
			return ERR_DB_ERR, err
		}
	} else {
		if len(dao.Keyfields) == 0 {
			return ERR_IN_PARAM_INVALID, fmt.Errorf("缺少指定更新主键!!!")
		}
		upcnt, errsql, err = dao.InsertOrUpdateEx2(upconf.data, upconf.table, upconf.keyfields, upconf.ignoreupdatefields, upconf.ignoreinsertfields, nil)
		if err != nil {
			utils.Log.WarnTypef("DB", "sql:%s, err:%s", errsql, err)
			upconf.err_sql = errsql
			return ERR_DB_ERR, err
		}
	}

	//req.FindEx(TYPE_AFTER_UPDATE, dao, req);

	if req.OnActionEx != nil {
		if upcnt > 0 {
			if ok, respflag := req.OnActionEx(TYPE_AFTER_UPDATE, dao, req.ReqR.URL, uptype); ok {
				if respflag == 1 {
					return
				}
			}
		}
	}

	req.Resp.SetKeyValues("code", 0, "upcnt", upcnt)
	if upconf.tracesql == 1 && tracesqlBuilder.Len() > 0 {
		req.Resp.SetKeyValue("tracesql", tracesqlBuilder.String())
	}

	if req.BeforeResponse != nil {
		if req.BeforeResponse() {
			req.ResponseAsJSON()
		}
	} else {
		req.ResponseAsJSON()
	}
	return
}

/*
	{
	   "id":"basic.goods",
	   "single": true,    // 返回到data中
	   "args":{
	       "id":0
	    },
	   "page":1,
	   "pagesize":10
	}

	{
	   "list":[{}]
	}
*/
func OnHttpDBCommQry(req *PrjHttpUtils.HttpRec) (errcode int, err error) {
	datasourceid := req.QryAsStringEx("__datasource", "", true)
	if len(datasourceid) == 0 {
		datasourceid = req.QryAsStringEx("datasourceid", "", true)
	}

	dbname := req.QryAsStringEx("__dbname", "", true)

	resid := req.QryAsStringEx("__resid", "", true)
	if len(resid) == 0 {
		resid = req.QryAsStringEx("id", "", true)
		if len(resid) == 0 {
			return ERR_INNER, fmt.Errorf("请指定资源ID(resid)[%s]", resid)
		}
	}

	singlekeyname := req.QryAsStringEx("__single", "", true)
	if len(singlekeyname) > 0 {
		if utils.StrIndexIgnoreCase(singlekeyname, "true", "1", "是") != -1 {
			singlekeyname = "data"
		}
	} else {
		singlekeyname := req.QryAsStringEx("single", "", true)
		if len(singlekeyname) > 0 {
			if utils.StrIndexIgnoreCase(singlekeyname, "true", "1", "是") != -1 {
				singlekeyname = "data"
			}
		}
	}

	args := req.Req.ValueByName("args")
	if args == nil {
		args = req.Req
	}

	req.RangeURLParam(func(k, v string) bool {
		strs := strings.SplitN(k, ".", 2)
		if len(strs) == 2 && strs[0] == "args" {
			if args == nil {
				args = wrapper.NewSVObject()
			}
			args.SetKeyString(strs[1], v)
		}
		return true
	})

	return OnHttpDBCommQryEx(datasourceid, dbname, resid, singlekeyname, args, req)
}

func OnHttpDBCommExec(req *PrjHttpUtils.HttpRec) (errcode int, err error) {
	datasourceid := req.QryAsStringEx("__datasource", "", true)
	if len(datasourceid) == 0 {
		datasourceid = req.QryAsStringEx("datasourceid", "", true)
	}

	dbname := req.QryAsStringEx("__dbname", "", true)

	resid := req.QryAsStringEx("__resid", "", true)
	if len(resid) == 0 {
		resid = req.QryAsStringEx("id", "", true)
		if len(resid) == 0 {
			return ERR_INNER, fmt.Errorf("请指定资源ID(resid)[%s]", resid)
		}
	}

	var dbresrec *wrapper.SuperValue
	if req.OnActionEx != nil {
		if ok, obj := req.OnActionEx(TYPE_GET_RESID, resid); ok && obj != nil { // 通过事件去获取
			dbresrec = obj.(*wrapper.SuperValue)
			if dbresrec == nil {
				return ERR_INNER, fmt.Errorf("无法找到对应的数据资源[%s]", resid)
			}
		}
	}
	if dbresrec == nil {
		dbresrec = GetDBRes(resid)
		if dbresrec == nil {
			return ERR_INNER, fmt.Errorf("无法找到对应的数据资源[%s]", resid)
		}
	}

	if !checkVarByResConf(dbresrec.ValueByName("check"), req) {
		return
	}

	args := req.Req.ValueByName("args")
	if args == nil {
		args = req.Req
	}

	req.RangeURLParam(func(k, v string) bool {
		strs := strings.SplitN(k, ".", 2)
		if len(strs) == 2 && strs[0] == "args" {
			if args == nil {
				args = wrapper.NewSVObject()
			}
			args.SetKeyString(strs[1], v)
		}
		return true
	})

	resStep := req.QryAsStringEx("__resstep", "", true)
	if len(resStep) == 0 {
		resStep = "$exec"
	}

	sql := GetDBResSQLEx(dbresrec, resStep, args)
	if len(sql) == 0 {
		return 503, fmt.Errorf("%s对应的%s没有进行配置", resid, resStep)
	}

	sqlconf := dbresrec.ValueByName(resStep)
	if DBComm_Debug_Mode == 1 {
		utils.Log.DebugTypef("DB", "%s", sql)
	}

	if sqlconf.IntByName("__tracesql", 0) == 1 {
		//utils.Log.DebugTypef("DB", "%s", qrysql)
		req.Resp.SetKeyValue("tracesql", sql)
	}

	var dao *PrjDBUtils.DAOHelper
	if len(dbname) > 0 {
		dao = PrjDBUtils.NewDAOHelperEx(datasourceid, dbname)
	} else {
		dao = PrjDBUtils.NewDAOHelper(datasourceid)
	}

	defer dao.Close()
	if req.OnActionEx != nil {
		req.OnActionEx(TYPE_INIT_DAO, dao)
	}

	n, errsql, err1 := dao.ExecuteBatchSQL(sql)
	if err1 != nil {
		req.ResponseJSONValues("code", -1, "errsql", errsql, "msg", err1.Error())
		return
	} else {
		req.ResponseJSONValues("code", 0, "upcnt", n)
	}
	return
}

/*
args   // 前端传入的

	{
	   "id":0
	}

single:"data"

	{
	   "data":{
	    }
	}

	{
	   "list":[{}]
	}
*/
func OnHttpDBCommQryEx(datasourceid, dbname string, resid string, singlekeyname string, args *wrapper.SuperValue, req *PrjHttpUtils.HttpRec) (errcode int, err error) {
	if len(resid) == 0 {
		return ERR_INNER, fmt.Errorf("未指定资源id(id)")
	}
	if len(datasourceid) == 0 {
		datasourceid = Default_DatasourceId
		if len(datasourceid) == 0 {
			return ERR_INNER, fmt.Errorf("未指定数据源id(datasourceid)")
		}
	}

	var dbresrec *wrapper.SuperValue
	if req.OnActionEx != nil {
		if ok, obj := req.OnActionEx(TYPE_GET_RESID, resid); ok && obj != nil {
			dbresrec = obj.(*wrapper.SuperValue)
			if dbresrec == nil {
				return ERR_INNER, fmt.Errorf("无法找到对应的数据资源[%s]", resid)
			}
		}
	}
	if dbresrec == nil {
		dbresrec = GetDBRes(resid)
		if dbresrec == nil {
			return ERR_INNER, fmt.Errorf("无法找到对应的数据资源[%s]", resid)
		}
	}

	if !checkVarByResConf(dbresrec.ValueByName("check"), req) {
		return
	}

	dbresrec.CheckStrFunc("__datasource", func(strv string) {
		datasourceid = strv
	})

	sqlconf := dbresrec.ValueByName("$sel")
	if sqlconf == nil {
		return ERR_INNER, fmt.Errorf("配置非法[%s],找不到对应的[$sel]配置", resid)
	}
	sqltype := sqlconf.IntByName("type", 0)
	if sqltype == 0 {
		return OnHttpDBCommQryResType0(datasourceid, dbname, sqlconf, singlekeyname, args, req)
	} else if sqltype == 1 {
		return OnHttpDBCommQryResType1(datasourceid, dbname, sqlconf, singlekeyname, args, req)
	} else if sqltype == 2 {
		return OnHttpDBCommQryResType2(datasourceid, dbname, sqlconf, singlekeyname, args, req)
	}

	return
}

func OnHttpDBCommQryResType0(datasourceid, dbname string, sqlconf *wrapper.SuperValue, singlekeyname string, args *wrapper.SuperValue, req *PrjHttpUtils.HttpRec) (errcode int, err error) {
	sql_args_conf := sqlconf.ValueByName("args")

	sql_templ := sqlconf.StringByPath("sql", "")
	var jsonFeilds []string
	str := sqlconf.StringByPath("jsonfields", "")
	if len(str) > 0 {
		jsonFeilds = strings.Split(str, ",")
	}

	hasWhereFlag := false
	if !sqlconf.CheckExistsIntFunc("whereflag", func(val int64) {
		hasWhereFlag = val == 1 // 如果有设定直接读取设定的值
	}) {
		hasWhereFlag = strings.Contains(strings.ToLower(sql_templ), "where") // 自动判断
	}

	fmt.Println(args.String())

	lstKey := Default_List_KeyName

	var dao *PrjDBUtils.DAOHelper
	if len(dbname) > 0 {
		dao = PrjDBUtils.NewDAOHelperEx(datasourceid, dbname)
	} else {
		dao = PrjDBUtils.NewDAOHelper(datasourceid)
	}
	defer dao.Close()

	dao.MaxPackageRowCount = MAX_PACKAGE_RECORD_SIZE

	if req.OnActionEx != nil {
		req.OnActionEx(TYPE_INIT_DAO, dao)
	}

	page := req.QryAsInt(KEY_PAGE_INDEX, -1)
	pagesize := req.QryAsInt(KEY_PAGE_SIZE, proj_page_size)
	reqtotal := req.QryAsInt(KEY_REQ_CNT, 1)
	qry_posi := req.QryAsInt(KEY_POSI, -1)
	qry_num := req.QryAsInt(KEY_POSI_NUM, proj_page_size)

	resp := req.Resp

	var innererr error = nil

	_, qrysql, err := dao.QueryCustom(func() string {
		if len(singlekeyname) > 0 {
			return ""
		}
		if page == -1 {
			return ""
		}
		if reqtotal == 0 {
			return ""
		}
		cntsql := sqlconf.StringByName("countsql", "")
		if len(cntsql) > 0 {
			return cntsql
		}
		cntsql = strings.ReplaceAll(sql_templ, "$fields$", "count(1)")
		return cntsql
	}, func(sb *db_utils.SQLBuilder) {
		sql := sql_templ
		fields := sqlconf.StringByName("fields", "")
		if len(fields) > 0 {
			sql = strings.ReplaceAll(sql, "$fields$", fields)
		}

		sb.AddAny(sql)
		if hasWhereFlag {
			sb.Whereflag = 1
		}

	}, func(wb *db_utils.SQLBuilder) {

		{
			fixedCondi := sql_args_conf.ValueByName("__fixed")
			if fixedCondi != nil {
				fixedCondi.Range(func(key string, val *wrapper.SuperValue) bool {
					wb.CheckAnd()
					wb.AddAny(val.String())
					return true
				})
			}
		}

		if args == nil || args.Count() == 0 {
			return
		}

		args_where_conf := sql_args_conf.ValueByName("where")
		if args_where_conf == nil {
			args_where_conf = sql_args_conf
		}
		if args_where_conf == nil {
			return
		}
		args_where_conf.Range(func(key string, val *wrapper.SuperValue) bool {
			if key == "__fixed" {
				return true
			}
			v1 := args.StringByName(key, "")
			if len(v1) == 0 {
				if ok, v2 := dao.GetParamVal(key); ok {
					v1 = fmt.Sprintf("%v", v2)
				}
			}
			if len(v1) > 0 {
				var str string
				if val.IsObject() {
					str = val.StringByName("value", "")
				} else {
					str = val.String()
				}
				str = strings.ReplaceAll(str, fmt.Sprintf("$%s$", key), v1)
				wb.CheckAnd()
				wb.AddAny(str)

			}
			return true
		})

	}, func(sb *db_utils.SQLBuilder) {
		orderby := req.QryAsString("orderby", "")
		if len(orderby) == 0 && sqlconf != nil {
			orderby = sqlconf.StringByName("orderby", "")
		}
		if len(orderby) > 0 {
			sb.OrderBy().AddAny(orderby)
		}
	}, func() (posi int, num int) {
		if qry_posi >= 0 {
			return qry_posi, qry_num
		}
		if page >= 1 {
			return (page - 1) * pagesize, pagesize
		}
		return 0, 0
	}, func(rec *wrapper.SuperValue, k *string, v *interface{}, rowval []db_utils.SqlValue, ignore *bool) {
		if utils.StrIndex(*k, jsonFeilds...) >= 0 {
			v1 := *v
			if buf, ok := v1.([]byte); ok {
				json, err := wrapper.NewSVFromBuf(buf)
				if err != nil {
					*v = nil
				}
				*v = json
			}
		}
	}, func(sql *string) {
		args_conf := sql_args_conf.ValueByName("conf")
		if args_conf == nil {
			return
		}
		args_conf.Range(func(key string, val *wrapper.SuperValue) bool {
			stype := val.StringByName("type", "repl")

			strv := ""
			if ok, v := dao.GetParamVal(key); ok {
				strv = fmt.Sprintf("%v", v)
			} else {
				strv = args.StringByName(key, "") // 获取传入的值
			}

			if len(strv) == 0 && val.BoolByName("must", false) {
				innererr = fmt.Errorf("[%s] 必须指定一个值", key)
				return false
			}

			if stype == "repl" {

				*sql = strings.ReplaceAll(*sql, fmt.Sprintf("$%s$", key), strv)
			}
			return true
		})
	}, func() *wrapper.SuperValue {
		if len(singlekeyname) > 0 {
			return resp.ForceByPath(singlekeyname)
		}
		return resp.ForceArrayByPath(lstKey)
	}, func(cnt int) {
		resp.SetKeyValue("allcount", cnt)
		resp.SetKeyValue("allpage", math.Ceil(float64(cnt)/float64(pagesize)))
	})

	if innererr != nil {
		req.Resp.SetKeyValues("code", -1, "errcode", ERR_DB_ERR, "msg", innererr.Error())
	} else if err != nil {
		req.Resp.SetKeyValues("code", -1, "errcode", ERR_DB_ERR, "msg", err.Error())
	} else {
		req.Resp.SetKeyValues("code", 0)
	}

	if DBComm_Debug_Mode == 1 {
		utils.Log.DebugTypef("DB", "%s", qrysql)
	}

	if sqlconf.IntByName("__tracesql", 0) == 1 {
		//utils.Log.DebugTypef("DB", "%s", qrysql)
		req.Resp.SetKeyValue("tracesql", qrysql)
	}

	if req.BeforeResponse != nil {
		if req.BeforeResponse() {
			req.CheckResponseAsJson()
		}
	} else {
		req.CheckResponseAsJson()
	}
	return
}

func OnHttpDBCommQryResType1(datasourceid, dbname string, sqlconf *wrapper.SuperValue, singlekeyname string, args *wrapper.SuperValue, req *PrjHttpUtils.HttpRec) (errcode int, err error) {
	sql_templ := sqlconf.StringByPath("sql", "")
	var jsonFeilds []string
	str := sqlconf.StringByPath("jsonfields", "")
	if len(str) > 0 {
		jsonFeilds = strings.Split(str, ",")
	}

	lstKey := Default_List_KeyName

	var dao *PrjDBUtils.DAOHelper
	if len(dbname) > 0 {
		dao = PrjDBUtils.NewDAOHelperEx(datasourceid, dbname)
	} else {
		dao = PrjDBUtils.NewDAOHelper(datasourceid)
	}
	defer dao.Close()
	dao.MaxPackageRowCount = MAX_PACKAGE_RECORD_SIZE
	if req.OnActionEx != nil {
		req.OnActionEx(TYPE_INIT_DAO, dao)
	}

	page := req.QryAsInt(KEY_PAGE_INDEX, -1)
	pagesize := req.QryAsInt(KEY_PAGE_SIZE, proj_page_size)
	reqtotal := req.QryAsInt(KEY_REQ_CNT, 1)
	qry_posi := req.QryAsInt(KEY_POSI, -1)
	qry_num := req.QryAsInt(KEY_POSI_NUM, proj_page_size)

	resp := req.Resp

	_, qrysql, err := dao.QueryCustomEx(func() string {
		if len(singlekeyname) > 0 {
			return ""
		}
		if page == -1 {
			return ""
		}
		if reqtotal == 0 {
			return ""
		}
		cntsql := sqlconf.StringByName("countsql", "")
		if len(cntsql) > 0 {
			return cntsql
		}
		cntsql = strings.ReplaceAll(sql_templ, "$fields$", "count(1)")
		return cntsql
	}, func() string {
		sql := sql_templ
		fields := sqlconf.StringByName("fields", "")
		if len(fields) > 0 {
			sql = strings.ReplaceAll(sql, "$fields$", fields)
		}
		return sql
	}, func(rec *wrapper.SuperValue, k *string, v *interface{}, rowval []db_utils.SqlValue, ignore *bool) {
		if utils.StrIndex(*k, jsonFeilds...) >= 0 {
			v1 := *v
			if buf, ok := v1.([]byte); ok {
				json, err := wrapper.NewSVFromBuf(buf)
				if err != nil {
					*v = nil
				}
				*v = json
			}
		}
	}, func(sql *string, action int) {
		args.Range(func(key string, val *wrapper.SuperValue) bool {
			str := val.String()
			*sql = strings.ReplaceAll(*sql, fmt.Sprintf("$%s$", key), str)
			return true
		})

		if action == 1 {
			*sql = strings.ReplaceAll(*sql, "$ORDERBY$", "")
			*sql = strings.ReplaceAll(*sql, "$PAGE$", "")
		} else {
			var pagesql string = ""
			if qry_posi >= 0 {
				pagesql = fmt.Sprintf(" LIMIT %d, %d ", qry_posi, qry_num)
			} else if page >= 1 {
				pagesql = fmt.Sprintf(" LIMIT %d, %d ", (page-1)*pagesize, pagesize)
			}
			*sql = strings.ReplaceAll(*sql, "$PAGE$", pagesql)

			orderby := req.QryAsString("orderby", "")
			if len(orderby) == 0 && sqlconf != nil {
				orderby = sqlconf.StringByName("orderby", "")
			}
			*sql = strings.ReplaceAll(*sql, "$ORDERBY$", orderby)
		}

	}, func() *wrapper.SuperValue {
		if len(singlekeyname) > 0 {
			return resp.ForceByPath(singlekeyname)
		}
		return resp.ForceArrayByPath(lstKey)
	}, func(cnt int) {
		resp.SetKeyValue("allcount", cnt)
		resp.SetKeyValue("allpage", math.Ceil(float64(cnt)/float64(pagesize)))
	})

	if err != nil {
		//utils.Log.WarnTypef("DB", "%s, 异常:%s", qrysql, err.Error())
		req.Resp.SetKeyValues("code", -1, "errcode", ERR_DB_ERR, "msg", err.Error())
	} else {
		req.Resp.SetKeyValues("code", 0)
	}

	if sqlconf.IntByName("__tracesql", 0) == 1 {
		//utils.Log.DebugTypef("DB", "%s", qrysql)
		req.Resp.SetKeyValue("tracesql", qrysql)
	}
	if req.BeforeResponse != nil {
		if req.BeforeResponse() {
			req.CheckResponseAsJson()
		}
	} else {
		req.CheckResponseAsJson()
	}
	return
}

func OnHttpDBCommQryResType2(datasourceid, dbname string, sqlconf *wrapper.SuperValue, singlekeyname string, args *wrapper.SuperValue, req *PrjHttpUtils.HttpRec) (errcode int, err error) {
	sql_templ := sqlconf.StringByPath("sql", "")
	var jsonFeilds []string
	str := sqlconf.StringByPath("jsonfields", "")
	if len(str) > 0 {
		jsonFeilds = strings.Split(str, ",")
	}

	lstKey := Default_List_KeyName

	var dao *PrjDBUtils.DAOHelper
	if len(dbname) > 0 {
		dao = PrjDBUtils.NewDAOHelperEx(datasourceid, dbname)
	} else {
		dao = PrjDBUtils.NewDAOHelper(datasourceid)
	}
	defer dao.Close()
	dao.MaxPackageRowCount = MAX_PACKAGE_RECORD_SIZE
	if req.OnActionEx != nil {
		req.OnActionEx(TYPE_INIT_DAO, dao)
	}

	page := req.QryAsInt(KEY_PAGE_INDEX, -1)
	pagesize := req.QryAsInt(KEY_PAGE_SIZE, proj_page_size)
	reqtotal := req.QryAsInt(KEY_REQ_CNT, 1)
	qry_posi := req.QryAsInt(KEY_POSI, -1)
	qry_num := req.QryAsInt(KEY_POSI_NUM, proj_page_size)

	resp := req.Resp

	_, qrysql, err := dao.QueryCustomEx(func() string {
		if len(singlekeyname) > 0 {
			return ""
		}
		if page == -1 {
			return ""
		}
		if reqtotal == 0 {
			return ""
		}
		cntsql := sqlconf.StringByName("countsql", "")
		if len(cntsql) > 0 {
			return cntsql
		}
		cntsql = strings.ReplaceAll(sql_templ, "$fields$", "count(1)")
		return cntsql
	}, func() string {
		sql := sql_templ
		fields := sqlconf.StringByName("fields", "")
		if len(fields) > 0 {
			sql = strings.ReplaceAll(sql, "$fields$", fields)
		}
		return sql
	}, func(rec *wrapper.SuperValue, k *string, v *interface{}, rowval []db_utils.SqlValue, ignore *bool) {
		if utils.StrIndex(*k, jsonFeilds...) >= 0 {
			v1 := *v
			if buf, ok := v1.([]byte); ok {
				json, err := wrapper.NewSVFromBuf(buf)
				if err != nil {
					*v = nil
				}
				*v = json
			}
		}
	}, func(sql *string, action int) {

		// where条件
		sql_args_conf := sqlconf.ValueByName("args")
		wb := db_utils.NewSQLBuilder()
		{
			fixedCondi := sql_args_conf.ValueByName("__fixed")
			if fixedCondi != nil {
				fixedCondi.Range(func(key string, val *wrapper.SuperValue) bool {
					wb.CheckAnd()
					wb.AddAny(val.String())
					return true
				})
			}
		}

		if args != nil && args.Count() > 0 {
			args_where_conf := sql_args_conf.ValueByName("where")
			if args_where_conf == nil {
				args_where_conf = sql_args_conf
			}
			if args_where_conf == nil {
				return
			}
			args_where_conf.Range(func(key string, val *wrapper.SuperValue) bool {
				if key == "__fixed" {
					return true
				}
				v1 := args.StringByName(key, "")
				if len(v1) == 0 {
					if ok, v2 := dao.GetParamVal(key); ok {
						v1 = fmt.Sprintf("%v", v2)
					}
				}
				if len(v1) > 0 {
					var str string
					if val.IsObject() {
						str = val.StringByName("value", "")
					} else {
						str = val.String()
					}
					str = strings.ReplaceAll(str, fmt.Sprintf("$%s$", key), v1)
					wb.CheckAnd()
					wb.AddAny(str)

				}
				return true
			})
		}
		if wb.Len() > 0 {
			strCondi := wb.String()
			*sql = strings.ReplaceAll(*sql, "$CONDI$", " AND "+strCondi)
		} else {
			*sql = strings.ReplaceAll(*sql, "$CONDI$", "")
		}

		// end where条件

		if action == 1 {
			*sql = strings.ReplaceAll(*sql, "$ORDERBY$", "")
			*sql = strings.ReplaceAll(*sql, "$PAGE$", "")
		} else {
			var pagesql string = ""
			if qry_posi >= 0 {
				pagesql = fmt.Sprintf(" LIMIT %d, %d ", qry_posi, qry_num)
			} else if page >= 1 {
				pagesql = fmt.Sprintf(" LIMIT %d, %d ", (page-1)*pagesize, pagesize)
			}
			*sql = strings.ReplaceAll(*sql, "$PAGE$", pagesql)

			orderby := req.QryAsString("orderby", "")
			if len(orderby) == 0 && sqlconf != nil {
				orderby = sqlconf.StringByName("orderby", "")
			}
			if len(orderby) > 0 {
				orderby = "ORDER BY " + orderby
			}
			*sql = strings.ReplaceAll(*sql, "$ORDERBY$", orderby)
		}

	}, func() *wrapper.SuperValue {
		if len(singlekeyname) > 0 {
			return resp.ForceByPath(singlekeyname)
		}
		return resp.ForceArrayByPath(lstKey)
	}, func(cnt int) {
		resp.SetKeyValue("allcount", cnt)
		resp.SetKeyValue("allpage", math.Ceil(float64(cnt)/float64(pagesize)))
	})

	if err != nil {
		//utils.Log.WarnTypef("DB", "%s, 异常:%s", qrysql, err.Error())
		req.Resp.SetKeyValues("code", -1, "errcode", ERR_DB_ERR, "msg", err.Error())
	} else {
		req.Resp.SetKeyValues("code", 0)
	}

	if sqlconf.IntByName("__tracesql", 0) == 1 {
		//utils.Log.DebugTypef("DB", "%s", qrysql)
		req.Resp.SetKeyValue("tracesql", qrysql)
	}
	if req.BeforeResponse != nil {
		if req.BeforeResponse() {
			req.CheckResponseAsJson()
		}
	} else {
		req.CheckResponseAsJson()
	}
	return
}
